package exam;

import examinee.ExamineeVar;
import utils.DBConnection;
import utils.TokenUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: Shawn
 * Date: 2009-6-25
 * Time: 23:16:49
 */
public class TestPaper {

    public static long addTestPaper(Connection con, TestPaperInfo info) throws SQLException {
        String sql = "INSERT INTO testpaper VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, info.getEid());
        ps.setLong(2, info.getDate());
        ps.setLong(3, info.getStartTime());
        ps.setLong(4, info.getEndTime());
        ps.setLong(5, info.getTime());
        ps.setInt(6, info.getState());
        ps.setInt(7, info.getScore());
        ps.setInt(8, info.getCheat());
        ps.executeUpdate();
        return DBConnection.getLastID(con);
    }

    public static void delTestPaper(Connection con, long tid) throws SQLException {
        String sql = "DELETE FROM testpaper WHERE tid=? LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, tid);
        ps.executeUpdate();
    }

    public static void setTime(Connection con, long tid, long time) throws SQLException {
        String sql = "UPDATE testpaper SET time=? WHERE tid=? LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, time);
        ps.setLong(2, tid);
        ps.executeUpdate();
    }

    public static void setDone(Connection con, long tid, int score) throws SQLException {
        String sql = "UPDATE testpaper SET endTime=?, state=?, score=? WHERE tid=? LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, System.currentTimeMillis());
        ps.setInt(2, ExamVar.STATE_END);
        ps.setInt(3, score);
        ps.setLong(4, tid);
        ps.executeUpdate();
    }

    public static void setCheat(Connection con, long tid, int cheat) throws SQLException {
        String sql = "UPDATE testpaper SET cheat=? WHERE tid=? LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, cheat);
        ps.setLong(2, tid);
        ps.executeUpdate();
    }

    public static TestPaperInfo getTestPaperByTid(Connection con, long tid) throws SQLException {
        String sql = "SELECT * FROM testpaper WHERE tid=? LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, tid);
        ResultSet rs = ps.executeQuery();
        return rs.next() ? rs2Info(rs) : new TestPaperInfo();
    }

    public static TestPaperInfo getLastTestPaper(Connection con, long eid) throws SQLException {
        String sql = "SELECT * FROM testpaper WHERE eid=? ORDER BY tid DESC LIMIT 1";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, eid);
        ResultSet rs = ps.executeQuery();
        return rs.next() ? rs2Info(rs) : new TestPaperInfo();
    }

    public static List<TestPaperInfo> getTestPapers(Connection con, long[] ids) throws SQLException {
        ArrayList<TestPaperInfo> list = new ArrayList<TestPaperInfo>();
        if (ids.length == 0) return list;

        String sql = "SELECT * FROM testpaper WHERE eid IN (" + TokenUtils.getTokenString(ids, ",") + ")";
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) list.add(rs2Info(rs));

        return list;
    }

    public static long getTestPapersSum(Connection con, long[] ids, int type) throws SQLException {
        if (ids.length == 0) return 0;

        String sql = "SELECT COUNT(*) AS sum FROM testpaper WHERE eid IN (" + TokenUtils.getTokenString(ids, ",") + ")";
        sql += getSearchSql(type);
        ResultSet rs = con.createStatement().executeQuery(sql);
        return rs.next() ? rs.getLong("sum") : 0;
    }

    public static List<TestPaperInfo> getTestPapers(Connection con, long[] ids, int type, long from, long length) throws SQLException {
        ArrayList<TestPaperInfo> list = new ArrayList<TestPaperInfo>();
        if (ids.length == 0) return list;

        String sql = "SELECT * FROM testpaper WHERE eid IN (" + TokenUtils.getTokenString(ids, ",") + ")";
        sql += getSearchSql(type) + " ORDER BY date DESC,eid LIMIT " + from + "," + length;

        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) list.add(rs2Info(rs));

        return list;
    }

    public static List<TestPaperInfo> getTestPapers(Connection con, long from, long to) throws SQLException {
        String sql = "SELECT * FROM testpaper WHERE date>=? AND date<=?";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setLong(1, from);
        ps.setLong(2, to == 0 ? System.currentTimeMillis() : to);
        ResultSet rs = ps.executeQuery();

        ArrayList<TestPaperInfo> list = new ArrayList<TestPaperInfo>();
        while (rs.next()) list.add(rs2Info(rs));
        return list;
    }

    private static TestPaperInfo rs2Info(ResultSet rs) throws SQLException {
        TestPaperInfo info = new TestPaperInfo();
        info.setTid(rs.getLong("tid"));
        info.setEid(rs.getLong("eid"));
        info.setDate(rs.getLong("date"));
        info.setStartTime(rs.getLong("startTime"));
        info.setEndTime(rs.getLong("endTime"));
        info.setTime(rs.getLong("time"));
        info.setState(rs.getInt("state"));
        info.setScore(rs.getInt("score"));
        info.setCheat(rs.getInt("cheat"));
        return info;
    }

    private static String getSearchSql(int type) {
        if (type == ExamineeVar.SCORE_PASS) {
            return " AND score>=" + ExamineeVar.PASS_SCORE;
        }
        else if (type == ExamineeVar.SCORE_NOT_PASS) {
            return " AND score!=-1 AND score<" + ExamineeVar.PASS_SCORE;
        }
        else if (type == ExamineeVar.SCORE_TESTED) {
            return " AND score!=-1";
        }
        else if (type == ExamineeVar.SOCRE_NOT_TEST) {
            return " AND score=-1";
        }
        else {
            return "";
        }
    }
}
